"""
项目仓库

提供项目相关数据的专门访问方法。
"""

from typing import List, Optional, Dict, Any
from datetime import datetime, timedelta

from sqlalchemy.orm import joinedload
from sqlalchemy import and_, or_, desc, asc, func, text

from src.core.di import Injectable, Inject
from src.database.repository import BaseRepository
from src.models import Project, Video
from src.models.project import project_videos


@Injectable(scope="singleton")
class ProjectRepository(BaseRepository[Project]):
    """
    项目仓库
    
    提供项目数据的专门访问方法和业务查询。
    """
    
    def __init__(self):
        super().__init__(Project)
    
    def get_with_videos(self, project_id: int) -> Optional[Project]:
        """获取包含视频的项目"""
        session = self.get_session()
        try:
            return session.query(Project).options(
                joinedload(Project.videos)
            ).filter(Project.id == project_id).first()
        finally:
            session.close()
    
    def find_active(self) -> List[Project]:
        """查找活跃项目"""
        return self.get_by_field("is_active", True)
    
    def find_templates(self) -> List[Project]:
        """查找模板项目"""
        return self.get_by_field("is_template", True)
    
    def find_by_name(self, name: str) -> Optional[Project]:
        """根据名称查找项目"""
        return self.get_one_by_field("name", name)
    
    def search_by_name(self, keyword: str) -> List[Project]:
        """根据名称关键词搜索项目"""
        return self.find(filters={"name": {"like": keyword}})
    
    def find_recent(self, days: int = 30) -> List[Project]:
        """查找最近创建的项目"""
        cutoff_date = datetime.utcnow() - timedelta(days=days)
        session = self.get_session()
        try:
            return session.query(Project).filter(
                Project.created_at >= cutoff_date
            ).order_by(desc(Project.created_at)).all()
        finally:
            session.close()
    
    def find_large_projects(self, min_videos: int = 10) -> List[Project]:
        """查找大型项目"""
        return self.find(
            filters={"video_count": {"gte": min_videos}},
            order_by="video_count",
            order_desc=True
        )
    
    def find_long_projects(self, min_duration: float = 3600) -> List[Project]:
        """查找长时间项目"""
        return self.find(
            filters={"total_duration": {"gte": min_duration}},
            order_by="total_duration",
            order_desc=True
        )
    
    def add_video_to_project(self, project_id: int, video_id: int, order_index: int = None) -> bool:
        """添加视频到项目"""
        session = self.get_session()
        try:
            project = session.query(Project).filter(Project.id == project_id).first()
            video = session.query(Video).filter(Video.id == video_id).first()
            
            if not project or not video:
                return False
            
            # 检查是否已存在
            existing = session.execute(
                text("SELECT 1 FROM project_videos WHERE project_id = :pid AND video_id = :vid"),
                {"pid": project_id, "vid": video_id}
            ).first()
            
            if existing:
                return False  # 已存在
            
            # 确定排序索引
            if order_index is None:
                max_order = session.execute(
                    text("SELECT MAX(order_index) FROM project_videos WHERE project_id = :pid"),
                    {"pid": project_id}
                ).scalar()
                order_index = (max_order or -1) + 1
            
            # 插入关联记录
            session.execute(
                text("""
                    INSERT INTO project_videos (project_id, video_id, order_index, added_at) 
                    VALUES (:pid, :vid, :order_idx, :added_at)
                """),
                {
                    "pid": project_id,
                    "vid": video_id,
                    "order_idx": order_index,
                    "added_at": datetime.utcnow()
                }
            )
            
            # 更新项目统计
            project.video_count += 1
            if video.duration:
                project.total_duration += video.duration
            
            session.commit()
            return True
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def remove_video_from_project(self, project_id: int, video_id: int) -> bool:
        """从项目中移除视频"""
        session = self.get_session()
        try:
            project = session.query(Project).filter(Project.id == project_id).first()
            video = session.query(Video).filter(Video.id == video_id).first()
            
            if not project or not video:
                return False
            
            # 删除关联记录
            deleted_count = session.execute(
                text("DELETE FROM project_videos WHERE project_id = :pid AND video_id = :vid"),
                {"pid": project_id, "vid": video_id}
            ).rowcount
            
            if deleted_count > 0:
                # 更新项目统计
                project.video_count = max(0, project.video_count - 1)
                if video.duration:
                    project.total_duration = max(0.0, project.total_duration - video.duration)
                
                session.commit()
                return True
            
            return False
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def get_project_videos(self, project_id: int, ordered: bool = True) -> List[Video]:
        """获取项目的视频列表"""
        session = self.get_session()
        try:
            query = session.query(Video).join(
                project_videos,
                Video.id == project_videos.c.video_id
            ).filter(
                project_videos.c.project_id == project_id
            )
            
            if ordered:
                query = query.order_by(project_videos.c.order_index)
            
            return query.all()
        finally:
            session.close()
    
    def reorder_project_videos(self, project_id: int, video_ids: List[int]) -> bool:
        """重新排序项目视频"""
        session = self.get_session()
        try:
            for index, video_id in enumerate(video_ids):
                session.execute(
                    text("""
                        UPDATE project_videos 
                        SET order_index = :order_idx 
                        WHERE project_id = :pid AND video_id = :vid
                    """),
                    {"order_idx": index, "pid": project_id, "vid": video_id}
                )
            
            session.commit()
            return True
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def duplicate_project(self, project_id: int, new_name: str) -> Optional[Project]:
        """复制项目"""
        session = self.get_session()
        try:
            original = session.query(Project).filter(Project.id == project_id).first()
            if not original:
                return None
            
            # 创建新项目
            new_project = Project(
                name=new_name,
                description=f"复制自: {original.name}",
                settings=original.settings,
                output_directory=original.output_directory,
                output_format=original.output_format,
                is_template=False
            )
            
            session.add(new_project)
            session.flush()  # 获取新项目ID
            
            # 复制视频关联
            video_associations = session.execute(
                text("""
                    SELECT video_id, order_index 
                    FROM project_videos 
                    WHERE project_id = :pid 
                    ORDER BY order_index
                """),
                {"pid": project_id}
            ).fetchall()
            
            for video_id, order_index in video_associations:
                session.execute(
                    text("""
                        INSERT INTO project_videos (project_id, video_id, order_index, added_at) 
                        VALUES (:pid, :vid, :order_idx, :added_at)
                    """),
                    {
                        "pid": new_project.id,
                        "vid": video_id,
                        "order_idx": order_index,
                        "added_at": datetime.utcnow()
                    }
                )
            
            # 更新新项目统计
            new_project.video_count = len(video_associations)
            
            # 计算总时长
            total_duration = session.execute(
                text("""
                    SELECT SUM(v.duration) 
                    FROM videos v 
                    JOIN project_videos pv ON v.id = pv.video_id 
                    WHERE pv.project_id = :pid
                """),
                {"pid": new_project.id}
            ).scalar()
            
            new_project.total_duration = total_duration or 0.0
            
            session.commit()
            session.refresh(new_project)  # 刷新对象以确保所有属性都已加载
            return new_project
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def get_project_statistics(self, project_id: int) -> Dict[str, Any]:
        """获取项目详细统计信息"""
        session = self.get_session()
        try:
            project = session.query(Project).filter(Project.id == project_id).first()
            if not project:
                return {}
            
            # 基本统计
            stats = {
                'video_count': project.video_count,
                'total_duration': project.total_duration,
                'total_duration_formatted': project.total_duration_formatted
            }
            
            # 视频格式分布
            format_stats = session.execute(
                text("""
                    SELECT v.format, COUNT(*) as count
                    FROM videos v
                    JOIN project_videos pv ON v.id = pv.video_id
                    WHERE pv.project_id = :pid AND v.format IS NOT NULL
                    GROUP BY v.format
                """),
                {"pid": project_id}
            ).fetchall()
            
            stats['format_distribution'] = {
                format_name: count for format_name, count in format_stats
            }
            
            # 分辨率分布
            resolution_stats = session.execute(
                text("""
                    SELECT CONCAT(v.width, 'x', v.height) as resolution, COUNT(*) as count
                    FROM videos v
                    JOIN project_videos pv ON v.id = pv.video_id
                    WHERE pv.project_id = :pid AND v.width IS NOT NULL AND v.height IS NOT NULL
                    GROUP BY v.width, v.height
                """),
                {"pid": project_id}
            ).fetchall()
            
            stats['resolution_distribution'] = {
                resolution: count for resolution, count in resolution_stats
            }
            
            # 文件大小统计
            size_stats = session.execute(
                text("""
                    SELECT 
                        SUM(v.file_size) as total_size,
                        AVG(v.file_size) as avg_size,
                        MIN(v.file_size) as min_size,
                        MAX(v.file_size) as max_size
                    FROM videos v
                    JOIN project_videos pv ON v.id = pv.video_id
                    WHERE pv.project_id = :pid
                """),
                {"pid": project_id}
            ).first()
            
            if size_stats and size_stats.total_size:
                stats['size_statistics'] = {
                    'total_size_bytes': size_stats.total_size,
                    'total_size_mb': size_stats.total_size / (1024 * 1024),
                    'avg_size_mb': (size_stats.avg_size or 0) / (1024 * 1024),
                    'min_size_mb': (size_stats.min_size or 0) / (1024 * 1024),
                    'max_size_mb': (size_stats.max_size or 0) / (1024 * 1024)
                }
            
            return stats
        finally:
            session.close()
    
    def get_global_statistics(self) -> Dict[str, Any]:
        """获取全局项目统计信息"""
        session = self.get_session()
        try:
            total_projects = self.count()
            active_projects = self.count(is_active=True)
            template_projects = self.count(is_template=True)
            
            # 项目大小分布
            size_distribution = session.execute(
                text("""
                    SELECT 
                        CASE 
                            WHEN video_count = 0 THEN 'empty'
                            WHEN video_count <= 5 THEN 'small'
                            WHEN video_count <= 20 THEN 'medium'
                            WHEN video_count <= 50 THEN 'large'
                            ELSE 'huge'
                        END as size_category,
                        COUNT(*) as count
                    FROM projects
                    GROUP BY size_category
                """)
            ).fetchall()
            
            return {
                'total_projects': total_projects,
                'active_projects': active_projects,
                'template_projects': template_projects,
                'inactive_projects': total_projects - active_projects,
                'size_distribution': {
                    category: count for category, count in size_distribution
                }
            }
        finally:
            session.close()
    
    def cleanup_empty_projects(self) -> int:
        """清理空项目"""
        empty_projects = self.find(filters={"video_count": 0})
        
        deleted_count = 0
        for project in empty_projects:
            if not project.is_template:  # 保留模板项目
                self.delete(project.id)
                deleted_count += 1
        
        return deleted_count
